﻿unit CollectorRepFrm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  cxGridExportLink,DateUtils, cxDataUtils,
  FIBQuery, pFIBCacheQueries,
  dxRibbonForm,cxGridDBTableView,
  Dialogs,  DBGridEh, MemTableDataEh, Db, ExtCtrls,
  JvAppIniStorage, JvComponentBase, JvFormPlacement, StdCtrls, ImgList,
  MemTableEh, ComCtrls, ToolWin, GridsEh, DBCtrlsEh, JvExMask,
  JvSpin, JvExStdCtrls, JvCombobox, JvExControls, JvXPCore, JvXPBar, FIBDataSet,
  pFIBDataSet, dxSkinsCore, dxSkinSeven, dxSkinscxPCPainter,
  cxCustomData, cxGraphics, cxDataStorage, cxEdit, cxDBData,
  cxGridCustomTableView, cxGridTableView, cxGridBandedTableView,
  cxGridDBBandedTableView, cxControls, cxGridCustomView, cxLocalization,
  cxGridCustomPopupMenu, cxGridPopupMenu, cxClasses, cxGridLevel, cxGrid,
  dxSkinsdxRibbonPainter, dxRibbon, dxSkinMcSkin, JvDialogs,
  cxLookAndFeelPainters, cxPropertiesStore, cxContainer, cxDropDownEdit,
  cxLookupEdit, cxDBLookupEdit, cxDBLookupComboBox, cxTextEdit, cxMaskEdit,
  cxCalendar, FIBDatabase, pFIBDatabase, dxSkinsdxStatusBarPainter, dxStatusBar,
  cxLabel, cxCheckBox, cxLookAndFeels, dxSkinOffice2010Blue,
  dxSkinOffice2010Silver, cxStyles, cxFilter, cxData, dxPSGlbl, dxPSUtl,
  dxPSEngn, dxPrnPg, dxBkgnd, dxWrap, dxPrnDev, dxPSCompsProvider,
  dxPSFillPatterns, dxPSEdgePatterns, dxPSPDFExportCore, dxPSPDFExport,
  cxDrawTextUtils, dxPSPrVwStd, dxPSPrVwAdv, dxPSPrVwRibbon,
  dxPScxEditorProducers, dxPScxExtEditorProducers, dxPScxPageControlProducer,
  dxSkinsdxBarPainter, dxBarSkinnedCustForm, dxPSCore, dxPScxCommon,
  dxPScxGrid6Lnk, cxSplitter, dxSkinOffice2010Black, Menus, DBGridEhGrouping;

type
  TCollectorRepForm = class(Tdxcustomribbonform)
    Panel1: TPanel;
    JvXPBar1: TJvXPBar;
    Panel2: TPanel;
    GroupBox1: TGroupBox;
    JvXPBar2: TJvXPBar;
    JvXPBar3: TJvXPBar;
    IsPrincipal: TCheckBox;
    Label2: TLabel;
    Label3: TLabel;
    KDS: TpFIBDataSet;
    CDS: TpFIBDataSet;
    KSource: TDataSource;
    CSource: TDataSource;
    DS: TpFIBDataSet;
    Panel3: TPanel;
    InfoPanel: TPanel;
    DSACCCODE: TFIBStringField;
    DSUSR: TFIBIntegerField;
    DSOPLATA: TFIBBCDField;
    DSDOLG: TFIBBCDField;
    DSKONTR: TFIBStringField;
    DSMASTER: TFIBStringField;
    DSSTREETNAME: TFIBStringField;
    DSHOUSE: TFIBStringField;
    DSFLAT: TFIBStringField;
    DSNAME: TFIBStringField;
    DSKONTRAGENTNAME: TFIBStringField;
    DataSource: TDataSource;
    IsCollector: TCheckBox;
    ADS: TpFIBDataSet;
    ASource: TDataSource;
    DSSTREET: TFIBStringField;
    DSSTATE: TFIBSmallIntField;
    DSSDOLG: TFIBBCDField;
    DSPCOUNT: TFIBIntegerField;
    l1: TcxGridLevel;
    grd1: TcxGrid;
    ppm1: TcxGridPopupMenu;
    localiz1: TcxLocalizer;
    v1: TcxGridDBBandedTableView;
    clSTATE: TcxGridDBBandedColumn;
    clACCCODE: TcxGridDBBandedColumn;
    clMASTER: TcxGridDBBandedColumn;
    clSTREETNAME: TcxGridDBBandedColumn;
    clHOUSE: TcxGridDBBandedColumn;
    clFLAT: TcxGridDBBandedColumn;
    clUSR: TcxGridDBBandedColumn;
    clNAME: TcxGridDBBandedColumn;
    clKONTR: TcxGridDBBandedColumn;
    clKONTRAGENTNAME: TcxGridDBBandedColumn;
    clSDOLG: TcxGridDBBandedColumn;
    clPCOUNT: TcxGridDBBandedColumn;
    clOPLATA: TcxGridDBBandedColumn;
    clDOLG: TcxGridDBBandedColumn;
    RibbonTab1: TdxRibbonTab;
    Ribbon: TdxRibbon;
    dlg1: TJvSaveDialog;
    clREESTR: TcxGridDBBandedColumn;
    DSREESTR: TIntegerField;
    prpstor1: TcxPropertiesStore;
    dt1: TcxDateEdit;
    dt2: TcxDateEdit;
    cbb_kontr: TcxLookupComboBox;
    cbb_collect: TcxLookupComboBox;
    trn1: TpFIBTransaction;
    table1: TMemTableEh;
    stat1: TdxStatusBar;
    xpbr2: TJvXPBar;
    SFGrid: TDBGridEh;
    tlb1: TToolBar;
    btnCheckAllBtn: TToolButton;
    btnUnCheckAll: TToolButton;
    btnSaveSrvBtn: TToolButton;
    btnOpenSrvBtn: TToolButton;
    SFTable: TMemTableEh;
    SFSource: TDataSource;
    il1: TImageList;
    clCode: TcxGridDBBandedColumn;
    cbb_utsk: TcxLookupComboBox;
    DS_UTSK: TpFIBDataSet;
    ds4: TDataSource;
    ch_srv: TcxCheckBox;
    chk_utsk: TCheckBox;
    cxspltr1: TcxSplitter;
    ch_noutsk: TcxCheckBox;
    cpr1: TdxComponentPrinter;
    lnk: TdxGridReportLink;
    clv1Flag: TcxGridDBBandedColumn;
    pm1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N3: TMenuItem;
    N4: TMenuItem;
    clcnt: TcxGridDBBandedColumn;
    clv1OPLATA_m: TcxGridDBBandedColumn;
    clv1SumOpl: TcxGridDBBandedColumn;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure JvXPBar2Click(Sender: TObject);
    procedure JvXPBar3Click(Sender: TObject);
    procedure cbb_kontrKeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure cbb_collectKeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure btnCheckAllBtnClick(Sender: TObject);
    procedure btnUnCheckAllClick(Sender: TObject);
    procedure btnSaveSrvBtnClick(Sender: TObject);
    procedure btnOpenSrvBtnClick(Sender: TObject);
    procedure SFGridDblClick(Sender: TObject);
    procedure SFGridKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure cbb_kontrPropertiesChange(Sender: TObject);
    procedure cbb_collectPropertiesChange(Sender: TObject);
    procedure ch_noutskPropertiesChange(Sender: TObject);
    procedure IsPrincipalClick(Sender: TObject);
    procedure cbb_kontrPropertiesInitPopup(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure InfoPanelDblClick(Sender: TObject);
    procedure clv1FlagPropertiesChange(Sender: TObject);
    procedure v1CellClick(Sender: TcxCustomGridTableView;
      ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
      AShift: TShiftState; var AHandled: Boolean);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure N4Click(Sender: TObject);
  private
    StartTime: TDateTime;
    fSchetList: TStringList;
    KList : TStringList;
    Srvutsk :string;
    procedure CreateMem;
    procedure AddTableData(Q :TFIBQuery);
    procedure SrvMark(AMark: boolean);
    procedure SrvListLoad;
    procedure SetSrvList(S: string);
    procedure SrvListSave;
    function GetSrvList: string;
    procedure CheckDubl;
    procedure UtscClear;
    function GetReestrByKontragent(kontragent :string) :string;
    function GetYM: string;
    function SchetWhereExpr(Num: integer): string;
    function AddSaldo(Num: Integer): Boolean;
    function AddSchet(Num: Integer): Boolean;
    procedure AddSchetLong;
    function IsUTSK :Boolean;
    procedure MakeNewEvent;
    procedure GetCountEvents;
  public
    procedure MakeReport;
  end;


var
  CollectorRepForm: TCollectorRepForm;

implementation
 uses  NameFrm, SrvSaveListFrm, {GridToXLS,} SrvFltUnit, cmUtils, DModule,
  NewMPFrm, GetMListFrm;

{$R *.dfm}

procedure TCollectorRepForm.SrvMark(AMark: boolean);
begin
 SFSource.DataSet:=nil;
 with SFTable do
  begin
   First;
   while not Eof do
    begin
     if FieldByName('Flag').AsBoolean<>AMark then
      begin
       Edit;
       FieldByName('Flag').AsBoolean:=AMark;
       Post;
      end;
     Next;
    end;
   First;
  end;
 SFSource.DataSet:=SFTable;
end;

procedure TCollectorRepForm.SrvListLoad;
 var Form: TSrvSaveListForm;
     S: string;
begin
 S:='';
 Form:=TSrvSaveListForm.Create(Application);
 if Form.ShowModal=mrOk then
    S:=Form.GetSrvList;
 Form.Free;
 if S<>'' then SetSrvList(S);
end;

function TCollectorRepForm.SchetWhereExpr(Num: integer): string;
const
  Step = 1400;
var
  i, Min, Count: Integer;
  S: string;
begin
  Result:='';
  Min:=Num*Step;
  Count:=Min+Step-1;
  if fSchetList.Count<=Count then
    Count:=fSchetList.Count-1;
  for I := Min to Count do
  begin
    if Result<>'' then Result:=Result+',';
    Result:=Result+#39+fSchetList[i]+#39;
  end;
end;

procedure TCollectorRepForm.SetSrvList(S: string);
begin
 SFSource.DataSet:=nil;
 with SFTable do
  begin
   First;
   while not Eof do
     begin
      Edit;
      FieldByName('Flag').AsBoolean:=Pos(';'+FieldByName('Srv').AsString+';', S)<>0;
      Post;
      Next;
     end;
   First;
  end;
 SFSource.DataSet:=SFTable;
end;

procedure TCollectorRepForm.SFGridDblClick(Sender: TObject);
begin
 SFTable.Edit;
 SFTable.FieldByName('Flag').AsBoolean:=not SFTable.FieldByName('Flag').AsBoolean;
 SFTable.Post;
end;

procedure TCollectorRepForm.SFGridKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
 case Key of
  VK_SPACE: SFGridDblClick(nil);
 end;
end;

procedure TCollectorRepForm.SrvListSave;
 var Form: TNameForm;
begin
 Form:=TNameForm.Create(Application);
 Form.SrvList:=GetSrvList;
 Form.ShowModal;
 Form.Free;
end;

function TCollectorRepForm.GetSrvList: string;
var
  s :string;
begin
 SFSource.DataSet:=nil;
 with SFTable do
  begin
   First;
   while not Eof do
    begin
     if FieldByName('Flag').AsBoolean then
       s:=s+#39+FieldByName('Srv').AsString+#39',';
     Next;
    end;
   First;
  end;
 SFSource.DataSet:=SFTable;
 if s<>'' then
       Delete(s,Length(s),1);
 Result:=s;
end;


function TCollectorRepForm.GetYM: string;
begin
  Result:=FormatDateTime('YY',DT1.Date)+FormatDateTime('MM', dt1.Date);
end;

procedure TCollectorRepForm.InfoPanelDblClick(Sender: TObject);
begin
 cpr1.Preview();
end;

procedure TCollectorRepForm.IsPrincipalClick(Sender: TObject);
begin
 if IsPrincipal.Checked=true then
        ch_noutsk.Checked:=False;
end;

//...........................TCollectorRepForm..................................
procedure TCollectorRepForm.btnCheckAllBtnClick(Sender: TObject);
begin
  SrvMark(True);
end;

procedure TCollectorRepForm.btnOpenSrvBtnClick(Sender: TObject);
begin
   SrvListLoad;
end;

procedure TCollectorRepForm.btnSaveSrvBtnClick(Sender: TObject);
begin
 SrvListSave;
end;

procedure TCollectorRepForm.btnUnCheckAllClick(Sender: TObject);
begin
 SrvMark(false);
end;

procedure TCollectorRepForm.cbb_collectKeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
 if Key=VK_DELETE then
 begin
   CDS.CloseOpen(false);
   IsCollector.Checked:=False;
 end;
end;

procedure TCollectorRepForm.cbb_collectPropertiesChange(Sender: TObject);
begin
  if cbb_kontr.Properties.ListSource=nil then
     cbb_kontr.Properties.ListSource:=KSource;
  IsCollector.Checked:=True;
end;

procedure TCollectorRepForm.cbb_kontrKeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key=VK_DELETE then
  begin
   // KDS.CloseOpen(true);
    cbb_kontr.Properties.ListSource:=nil;
    IsPrincipal.Checked:=False;
  end;
end;

procedure TCollectorRepForm.UtscClear;
begin
 cbb_utsk.Clear;
 cbb_utsk.Properties.Grid.DataController.Values
  [cbb_utsk.Properties.Grid.FocusedRowIndex,0]:='';
  cbb_utsk.Properties.ListSource:=nil;
 //DS_UTSK.CloseOpen(true);
end;

procedure TCollectorRepForm.v1CellClick(Sender: TcxCustomGridTableView;
  ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
  AShift: TShiftState; var AHandled: Boolean);
begin

end;

{------------------------------------------------------------------------------
GetReestrByKontragent:  Получаем кода реестров по коду контрагента
_______________________________________________________________________________}
function TCollectorRepForm.GetReestrByKontragent(kontragent :string) :string;
var
  Q :TFIBQuery;
  sql,s :string;
begin
  sql:='select nr.code from n_reestr nr where nr.kontragent=:kontragent order by nr.d1';
  Q:=GetQueryForUse(trn1,sql);
  try
   Q.ParamByName('kontragent').AsString:=kontragent;
   Q.ExecQuery;
   while not Q.Eof do
   begin
    s:=s+Q.FN('code').AsString+',';
    stat1.Panels[0].Text:=FormatDateTime('HH:MM:SS',Now-StartTime);
    Application.ProcessMessages;
    Q.Next;
   end;
  finally

  end;
  if s<>'' then Delete(s,Length(s),1);
  Result:=s;
  FreeQueryForUse(Q);
end;

procedure TCollectorRepForm.cbb_kontrPropertiesChange(Sender: TObject);
var
  sel :Integer;
  kontragent,reestr :string;
begin
 //при выборе контрагента получаем реестры
 sel:=cbb_kontr.Properties.Grid.FocusedRowIndex;
 kontragent:=VarToStr(cbb_kontr.Properties.Grid.DataController.Values[sel,0]);
 if kontragent='1-17' then
 begin
  if trn1.Active=false then trn1.Active:=True;
  cbb_utsk.Visible:=true;
  chk_utsk.Visible:=True;
  reestr:=GetReestrByKontragent(kontragent);
  if DS_UTSK.IsEmpty then
  begin
    DS_UTSK.Close;
    DS_UTSK.SelectSQL.Text:=
       'select code,name from spr$kontragent where isUtsk=1';
     {' select DISTINCT(sp.kontragent) code,sk.name'+
     ' from N_REESTRDATA nr'+
     ' left outer join spr$schet sp on sp.code=nr.accode'+
     ' left outer join spr$kontragent sk on sk.code=sp.kontragent'+
     ' where nr.reestr in ('+reestr+')';}
    DS_UTSK.CloseOpen(true);
  end;
   if cbb_utsk.Properties.ListSource=nil then
    cbb_utsk.Properties.ListSource:=ds4;
    cbb_utsk.ItemIndex:=0;
 end else
 begin
  if not DS_UTSK.IsEmpty then UtscClear;
  cbb_utsk.Visible:=False;
  chk_utsk.Checked:=False;
  chk_utsk.Visible:=False;
 end;
 IsPrincipal.Checked:=True;
end;

procedure TCollectorRepForm.cbb_kontrPropertiesInitPopup(Sender: TObject);
begin
 if cbb_kontr.Properties.ListSource=nil then
    cbb_kontr.Properties.ListSource:=KSource;
end;

procedure TCollectorRepForm.FormActivate(Sender: TObject);
begin
 SetTabActive('rep',True);
end;

procedure TCollectorRepForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  fSchetList.Free;
  KList.Free;
  RemoveBtn(Name);
  Action:=caFree;
end;

procedure TCollectorRepForm.FormCreate(Sender: TObject);
begin

 if FileExists(ExtractFilePath(Application.ExeName)+'lang\rus.ini') then
 begin
  localiz1.FileName:=ExtractFilePath(Application.ExeName)+'lang\rus.ini';
  localiz1.Active:=True;
  localiz1.Locale:=1049;
 end;
 AddButton(self);
 prpstor1.StorageName:=ExtractFilePath(Application.ExeName)+'config\collector';
 prpstor1.RestoreFrom;
 KDS.Open;  //Kontragent.Text:=KDS.FN('Name').AsString;
 CDS.Open;  //Collector.SelectNextValue(true);
 ADS.Open;
 SFGrid.DataSource:=nil;
 InitSFTable(SFTable);
  SFGrid.DataSource:=SFSource;
  SFGrid.AutoFitColWidths:=true;
 // создаем временную таблицу
 CreateMem;
 //список счетов
 fSchetList:=TStringList.Create;
 KList:=TStringList.Create;
end;

procedure TCollectorRepForm.CreateMem;
begin
  with table1.FieldDefs do
  begin
   Add('Flag',ftBoolean);
   Add('Code',ftInteger);
   Add('AccCode',ftString,32,True);
   Add('Usr',ftInteger);
   Add('SDolg',ftFloat);
   Add('PCount',ftInteger);
   Add('Oplata',ftFloat);
   Add('Oplata_m',ftFloat);
   Add('SumOpl',ftFloat);
   Add('Dolg',ftFloat);
   Add('Kontr',ftString,32);
   Add('Master',ftString,256);
   Add('Street',ftString,32);
   Add('StreetName',ftString,256);
   Add('House',ftString,32);
   Add('Flat',ftString,32);
   Add('Name',ftString,256);
   Add('KontragentName',ftString,256);
   Add('REESTR',ftInteger);
   Add('Srv',ftString,32760);
   Add('cnt',ftInteger);
  end;
  with table1.IndexDefs.AddIndexDef do
  begin
    Name:='INDEX';
    Fields:='AccCode';
  end;
  table1.CreateDataSet;
  table1.Open;
end;

procedure TCollectorRepForm.JvXPBar2Click(Sender: TObject);
var
  day :Integer;
begin
 day:= DaysBetween(dt1.Date, dt2.Date);
 if day>60 then
 begin
  if MessageDlg('Вы действительно хотите сформировать отчет за' + #13#10 +
                '                '+DateName(day)+
                ' ?',  mtWarning, [mbYes, mbNo], 0) = mrYes then
  begin
     MakeReport;
  end else Exit;
 end else MakeReport;
end;

procedure TCollectorRepForm.JvXPBar3Click(Sender: TObject);
begin
 //SaveAsExcel(Grid, nil, '');
 if dlg1.Execute then
 ExportGridToExcel(dlg1.FileName,grd1,false,true,True,'xls');
end;

function TCollectorRepForm.AddSaldo(Num: Integer): Boolean;
var
  Q: TFIBQuery;
  S, Srv, SList: string;
begin
  Result:=true;
  //формируем SQL Запрос
  S:='select AccCode, Summa, Srv, Kontragent from Saldo WHERE YM='#39+GetYM+#39;
  Srv:=GetSrvList;
  {if Srv<>'' then
    S:=S+' and Srv IN ('+Srv+')';}
  SList:=SchetWhereExpr(Num);
  if SList='' then Exit;
  S:=S+' and AccCode IN ('+SList+')';
  S:=S+' ORDER BY AccCode';
  //выполняем запрос
  Q:=GetQueryForUse(trn1, S);
  try
    Q.ExecQuery;
    //добавляем данные в таблицу в памяти
    while not Q.Eof do
    begin
      if Q.FN('AccCode').AsString=table1.FieldByName('AccCode').AsString then
      begin
        //KList.Values['Kontrgent']:='список Srv'
        if IsUTSK then
        S:=Srvutsk else
        S:=KList.Values[Q.FN('Kontragent').AsString];
        if (S='') or (Pos(';'+Q.FN('Srv').AsString+';', S)>0) then
        begin
          table1.Edit;
          table1.FieldByName('SDolg').AsCurrency:=
            table1.FieldByName('SDolg').AsCurrency+Q.FN('Summa').AsCurrency;
          table1.FieldByName('Dolg').AsCurrency:=
            table1.FieldByName('Dolg').AsCurrency+Q.FN('Summa').AsCurrency;
          table1.Post;
        end;
        stat1.Panels[0].Text:=FormatDateTime('HH:MM:SS',Now-StartTime);
        Application.ProcessMessages;
        Q.Next;
      end
      else
      while not table1.Eof do
      begin
        if Q.FN('AccCode').AsString=table1.FieldByName('AccCode').AsString then
          Break;
        table1.Next;
      end;
      if table1.Eof then Break;
    end;
  finally
    FreeQueryForUse(Q);
  end;

  Result:=False;
end;

function TCollectorRepForm.IsUTSK :Boolean;
begin
 if (IsPrincipal.Checked)  and
 (VarToStr(cbb_kontr.Properties.Grid.DataController.Values[
          cbb_kontr.Properties.Grid.FocusedRowIndex,0])='1-17') then
 Result:=true else
 Result:=False;
end;

procedure TCollectorRepForm.AddSchetLong;
var
  Q: TFIBQuery;
  S, Srv, SList,cnt: string;
  D, D1: TDateTime;
begin
  cnt:=IntToStr(table1.RecordCount);
  table1.First;
  while not table1.Eof do
  begin
    //--------------------получаем сальдо на начало
    //формируем SQL Запрос
    S:='select AccCode, Summa from Saldo WHERE YM='#39+GetYM+#39;
    if IsUTSK then
    Srv:=Srvutsk else
    Srv:=table1.FieldByName('Srv').AsString;
    if Srv<>'' then
      S:=S+' and pos('#39';'#39' || Srv || '#39';'#39', '#39+Srv+#39')<>0';
    S:=S+' and AccCode = '#39+table1.FieldByName('AccCode').AsString+#39;
    //выполняем запрос
    Q:=GetQueryForUse(trn1, S);
    try
      Q.ExecQuery;
      //добавляем данные в таблицу в памяти
      while not Q.Eof do
      begin
          table1.Edit;
          table1.FieldByName('SDolg').AsCurrency:=
            table1.FieldByName('SDolg').AsCurrency+Q.FN('Summa').AsCurrency;
          table1.FieldByName('Dolg').AsCurrency:=
            table1.FieldByName('Dolg').AsCurrency+Q.FN('Summa').AsCurrency;
          table1.Post;
          Q.Next;
        stat1.Panels[0].Text:=FormatDateTime('HH:MM:SS',Now-StartTime);
        Application.ProcessMessages;
      end;
    finally
      FreeQueryForUse(Q);
    end;

    //--------------------получаем оплаты
    D:=StrToDate('01.'+Copy(GetYM,3,2)+'.'+Copy(GetYM,1,2));
    D1:=D;
    if IsUTSK then
    Srv:=Srvutsk else
    Srv:=table1.FieldByName('Srv').AsString;
    S:='select AccCode, Summa, Prv_Type, Val_Date from SchetPrv WHERE Val_Date BETWEEN '+
      #39+DateToStr(D)+#39' and '#39+DateToStr(dt2.Date)+#39;
    if Srv<>'' then
      S:=S+' and pos('#39';'#39' || Srv || '#39';'#39', '#39+Srv+#39')<>0';
    S:=S+' and AccCode = '#39+table1.FieldByName('AccCode').AsString+#39;
    S:=S+' ORDER BY Val_Date';
    //выполняем запрос
    Q:=GetQueryForUse(trn1, S);
    try
      Q.ExecQuery;
      //добавляем данные в таблицу в памяти
      while not Q.Eof do
      begin
         table1.Edit;
          if (Q.FN('Prv_Type').AsInteger=1) and
            (Abs(Q.FN('Summa').AsCurrency)>=0.01) then
          begin
            if Q.FN('Summa').AsCurrency<0 then
            table1.FieldByName('Oplata').AsCurrency:=
            table1.FieldByName('Oplata').AsCurrency+Q.FN('Summa').AsCurrency*-1
            else
            table1.FieldByName('OPLATA_m').AsVariant:=
            table1.FieldByName('Oplata_m').AsCurrency+Q.FN('Summa').AsCurrency*-1;
            ///SumOpl
            table1.FieldByName('SumOpl').AsCurrency:=
            table1.FieldByName('SumOpl').AsCurrency+Q.FN('Summa').AsCurrency*-1;
            if Q.FN('Val_Date').AsDate<>D1 then
            begin
              table1.FieldByName('PCount').AsInteger:=
                table1.FieldByName('PCount').AsInteger+1;
              D1:=Q.FN('Val_Date').AsDate;
            end;
          end;
          table1.FieldByName('Dolg').AsCurrency:=
            table1.FieldByName('Dolg').AsCurrency+Q.FN('Summa').AsCurrency;
         table1.Post;
        stat1.Panels[0].Text:=FormatDateTime('HH:MM:SS',Now-StartTime);
        Application.ProcessMessages;
       Q.Next;
      end;
    finally
      FreeQueryForUse(Q);
    end;
    stat1.Panels[0].Text:=IntTostr(table1.RecNo)+' из '+cnt;
   Application.ProcessMessages;
   table1.Next;
  end;
end;

function TCollectorRepForm.AddSchet(Num: Integer): Boolean;
var
  Q: TFIBQuery;
  S, Srv, SList: string;
  D, D1: TDateTime;
begin
  Result:=true;
  //формируем SQL Запрос
  D:=StrToDate('01.'+Copy(GetYM,3,2)+'.'+Copy(GetYM,1,2));
  D1:=D;
  S:='select AccCode, Summa, Prv_Type, Val_Date,SRV, Kontragent from SchetPrv '+
     ' WHERE Val_Date BETWEEN '+
    #39+DateToStr(D)+#39' and '#39+DateToStr(dt2.Date)+#39;
  //если выбран список служб вручную
 { if ch_srv.Checked then
  begin
    Srv:=GetSrvList;
    if Srv<>'' then
      S:=S+' and Srv IN ('+Srv+')';
  end
  else
  begin
    //получаем список служб из таблицы [поле SRV]
    Srv:=table1.FieldByName('Srv').AsString;
    if Srv<>'' then
      S:=S+' and pos('#39';'#39' || Srv || '#39';'#39', '#39+Srv+#39')<>0';
  end;}
  SList:=SchetWhereExpr(Num);
  if SList='' then Exit;
  S:=S+' and AccCode IN ('+SList+')';
  S:=S+' ORDER BY AccCode, Val_Date';
  //выполняем запрос
  Q:=GetQueryForUse(trn1, S);
  try
    Q.ExecQuery;
    //добавляем данные в таблицу в памяти
    while not Q.Eof do
    begin
      if Q.FN('AccCode').AsString=table1.FieldByName('AccCode').AsString then
      begin
       if IsUTSK then
       S:=Srvutsk else
       S:=KList.Values[Q.FN('Kontragent').AsString];
       if (S='') or (Pos(';'+Q.FN('Srv').AsString+';', S)>0) then
       begin
        table1.Edit;
        if Q.FN('Val_Date').AsDate<dt1.Date then
        begin
          table1.FieldByName('SDolg').AsCurrency:=
            table1.FieldByName('SDolg').AsCurrency+Q.FN('Summa').AsCurrency;
          table1.FieldByName('Dolg').AsCurrency:=
            table1.FieldByName('Dolg').AsCurrency+Q.FN('Summa').AsCurrency;
        end
        else
        begin
          if (Q.FN('Prv_Type').AsInteger=1) and
            (Abs(Q.FN('Summa').AsCurrency)>=0.01) then
          begin
            if Q.FN('Summa').AsCurrency<0 then
            table1.FieldByName('Oplata').AsCurrency:=
            table1.FieldByName('Oplata').AsCurrency+Q.FN('Summa').AsCurrency*-1
            else
            table1.FieldByName('OPLATA_m').AsVariant:=
            table1.FieldByName('Oplata_m').AsCurrency+Q.FN('Summa').AsCurrency*-1;
            ///SumOpl
            table1.FieldByName('SumOpl').AsCurrency:=
            table1.FieldByName('SumOpl').AsCurrency+Q.FN('Summa').AsCurrency*-1;

            if Q.FN('Val_Date').AsDate<>D1 then
            begin
              table1.FieldByName('PCount').AsInteger:=
                table1.FieldByName('PCount').AsInteger+1;
              D1:=Q.FN('Val_Date').AsDate;
            end;
          end;
          table1.FieldByName('Dolg').AsCurrency:=
            table1.FieldByName('Dolg').AsCurrency+Q.FN('Summa').AsCurrency;
        end;
        table1.Post;
       end;
         stat1.Panels[0].Text:=FormatDateTime('HH:MM:SS',Now-StartTime);
         Application.ProcessMessages;
        Q.Next;
      end
      else
      while not table1.Eof do
      begin
        if (table1.FieldByName('PCount').AsInteger<>0) and
          (table1.FieldByName('SumOpl').AsCurrency=0) then
        begin
          table1.Edit;
          table1.FieldByName('PCount').AsInteger:=0;
          table1.Post;
        end;
        D1:=D;
        if Q.FN('AccCode').AsString=table1.FieldByName('AccCode').AsString then
          Break;
         stat1.Panels[0].Text:=FormatDateTime('HH:MM:SS',Now-StartTime);
         Application.ProcessMessages;
        table1.Next;
      end;
      if table1.Eof then Break;
    end;
  finally
    FreeQueryForUse(Q);
  end;

  Result:=False;
end;

procedure TCollectorRepForm.AddTableData(Q :TFIBQuery);
begin
  table1.Append;
  table1.FieldByName('Flag').AsBoolean:=False;
  table1.FieldByName('Code').AsInteger:=Q.FN('Code').AsInteger;
  table1.FieldByName('AccCode').AsString:=Q.FN('ACCODE').AsString;
  table1.FieldByName('Usr').AsInteger:=Q.FN('Usr').AsInteger;
  table1.FieldByName('SDolg').AsFloat:=0;
  table1.FieldByName('Dolg').AsFloat:=0;//Qmax.FN('summa').AsFloat;;
  table1.FieldByName('Kontr').AsString:=Q.FN('kontragent').AsString;
  table1.FieldByName('Master').AsString:=Q.FN('Master').AsString;
  table1.FieldByName('Street').AsString:=Q.FN('Street').AsString;
  table1.FieldByName('StreetName').AsString:=Q.FN('StreetName').AsString;
  table1.FieldByName('House').AsString:=Q.FN('House').AsString;
  table1.FieldByName('Flat').AsString:=Q.FN('Flat').AsString;
  table1.FieldByName('Name').AsString:=Q.FN('Name').AsString;
  table1.FieldByName('KontragentName').AsString:=Q.FN('KontragentName').AsString;
  table1.FieldByName('REESTR').AsInteger:=Q.FN('REESTR').AsInteger;
  table1.FieldByName('Srv').AsString:=Q.FN('Srv').AsString;
  table1.Post;
 end;

procedure TCollectorRepForm.MakeReport;
var
  Q1,Q :TFibQuery;
  sql,clause,oper,reestr,AccCode,utsk,utsk_kontr :string;
  Num: Integer;
begin
 StartTime:=Now;
 InfoPanel.Caption:='Формируется отчет. Ждите....';
 Application.ProcessMessages;
 //получаем список реестров из n_reestr
 if trn1.Active=false then trn1.Active:=True;
 if IsUTSK then
 begin
  utsk_kontr:=cbb_utsk.EditValue;
  //VarToStr(cbb_utsk.Properties.Grid.DataController.Values[
  //        cbb_utsk.Properties.Grid.FocusedRowIndex,0]);
  if  (utsk_kontr='1-1') or (utsk_kontr='1-17') then
  Srvutsk:=';4000115;4000113;' else
  Srvutsk:=';2548230148;2548137475;';
 end
 else
 begin
  srvutsk:='';
  Q:=GetQueryForUse(trn1,'Select code,srv from SPR$KONTRAGENT ');
  Q.ExecQuery;
  while not Q.Eof do
  begin
   KList.Values[Q.FN('Code').AsString]:=Q.FN('Srv').AsString;
   Q.Next;
  end;
  FreeQueryForUse(Q);
 end;
 clause:=''; oper:='';
 sql:='select nr.code from n_reestr nr'+
       ' WHERE nr.d1<='#39+DateToStr(Date)+#39+' and nr.d2>='+
                            #39+DateTostr(Date)+#39+
       ' and @@where_clause% 1 = 1 @';
 Q1:=GetQueryForUse(trn1,sql);
    //условие по умочанию
 Q1.ParamByName('where_clause').SetDefMacroValue;
  //если выбран УТСК
  if chk_utsk.Checked then
    utsk:=cbb_utsk.EditValue
    //VarToStr((cbb_utsk.Properties.Grid.DataController.Values
    //  [cbb_utsk.Properties.Grid.FocusedRowIndex,0]))
  else utsk:='';
  //если выбрали контрагента
   if IsPrincipal.Checked then
   begin
     if chk_utsk.Checked then
     clause:='nr.Kontragent='#39'1-17'#39 else
     clause:='nr.Kontragent='+#39+cbb_kontr.EditValue+#39;
   //VarToStr(cbb_kontr.Properties.Grid.DataController.Values[
   //       cbb_kontr.Properties.Grid.FocusedRowIndex,0])+#39;

   end;
 if ch_noutsk.Checked=True then
    clause:='nr.Kontragent<>'#39'1-17'#39;

 if (clause<>'') {and (chk_utsk.Checked=true)} then
     Q1.ParamByName('where_clause').AsString:=clause;
 Q1.ExecQuery; // получаем реестры
 reestr:=GetStrValue(Q1,'code');
  //если УТСК
 if IsPrincipal.Checked then
 if VarToStr(cbb_kontr.EditValue)='1-17' then
    clause:='';
  // если выбран утск принципал
 if utsk<>'' then
 begin
   clause:='nr.Kontragent='+#39+utsk+#39;
 end;
 // условие для реестра
 if reestr<>'' then
 begin
   if clause<>'' then clause:=clause+' AND ';
   clause:=clause+'nr.REESTR in ('+reestr+')';
 end;
  //если выбрали коллектора
 if IsCollector.Checked then
 begin
    if clause<>'' then clause:=clause+' AND ';
    clause:=clause+'nr.USR='+
      VarToStr(cbb_collect.EditValue);
 end;
   //если ничего не выбрали
  if (IsCollector.Checked=false) and (IsPrincipal.Checked=false) then
    raise Exception.Create('Нужно выбрать принципала или коллектора!');
 //получаем счета
 stat1.Panels[1].Text:='Собираем счета';
 Application.ProcessMessages;
 { TODO : Переделать запрос с учетом новых полей в n_reestrdata (kontragent,tgrk) }
  sql:=
'SELECT nr.CODE,nr.REESTR,nr.ACCODE,nr.USR,sp.kontragent,sk.name KontragentName,'+
      ' sk.srv,sp.master,sp.addr,ss.name StreetName,sa.Street,sa.House,sa.Flat,'+
      ' su.name'+
   ' FROM N_REESTRDATA nr'+
      ' left outer join spr$schet sp on sp.code=nr.accode'+
      ' left outer join spr$kontragent sk on sk.code=sp.kontragent'+
      ' left outer join spr$Addr sa on sa.code=sp.addr'+
      ' left outer join spr$Street ss on ss.code=sa.street'+
      ' left outer join spr$users su on su.code=nr.usr'+
       ' WHERE '+
       ' @@where_clause% 1 = 1 @';
  Q:=GetQueryForUse(trn1,sql);
  Q.ParamByName('where_clause').AsString:=clause;
  Q.OrderClause:='nr.ACCODE, nr.Code DESC';
  Q.ExecQuery;

  v1.DataController.DataSource:=nil;
  SetWaitCursor(true);
  // добавляем счета во временную таблицу
  stat1.Panels[1].Text:='Добавляем счета во временную таблицу...';
  Application.ProcessMessages;
  if not table1.IsEmpty then table1.EmptyTable;
  AccCode:='';
  while not Q.Eof do
  begin
    if Q.FN('ACCODE').AsString<>AccCode then
    begin
      AddTableData(Q);
      AccCode:=Q.FN('ACCODE').AsString;
       stat1.Panels[0].Text:=FormatDateTime('HH:MM:SS',Now-StartTime);
       Application.ProcessMessages;
    end;
    Q.Next;
  end;
  // table1.SortByFields('AccCode ASC,Code DESC');
  //удаляем двойные записи 1
  stat1.Panels[1].Text:='Корректируем записи...';
  Application.ProcessMessages;
  CheckDubl;
    //расчет начального сальдо
    stat1.Panels[1].Text:='расчет начального сальдо';
    Application.ProcessMessages;
    //получаем колво выполненых мероприятий
    GetCountEvents;
    //
    Num:=-1;
    table1.First;
    repeat
      Inc(Num);
    until AddSaldo(Num);
    //сканирование проводок
    stat1.Panels[1].Text:='сканирование проводок...';
    Application.ProcessMessages;
    Num:=-1;
    table1.First;
    repeat
      Inc(Num);
    until AddSchet(Num);
   stat1.Panels[1].Text:='';
  Application.ProcessMessages;

  SetWaitCursor;
  v1.DataController.DataSource:=DataSource;
  v1.ApplyBestFit();
  stat1.Panels[0].Text:='';
  InfoPanel.Caption:='Отчет сформирован. Время формирования: '+
                    FormatDateTime('HH:NN:SS',Now-StartTime);
end;

procedure TCollectorRepForm.GetCountEvents;
var
  Q :TFIBQuery;
  sql :string;

  function GetCount :integer;
  begin
    Q.Close;
    Q.MainWhereClause:='e.usr=:usr and e.eresult<>'#39#39+
                        ' and e.dactive between :d1 and :d2 and e.acccode=:acc';
    Q.ParamByName('usr').AsInteger:=table1.FieldByName('Usr').AsInteger;
    Q.ParamByName('d1').AsDateTime:=dt1.Date;
    Q.ParamByName('d2').AsDateTime:=dt2.Date;
    Q.ParamByName('acc').AsString:=table1.FieldByName('AccCode').AsString;
    Q.ExecQuery;
    Result:=Q.FN('count').AsInteger;
  end;
begin
  sql :='select count(*) from events e ';
  Q:=GetQueryForUse(dm1.trnRD,sql);
  with table1 do
  begin
    First;
    while not Eof do
    begin
      Edit;
      FieldByName('cnt').AsInteger:=GetCount;
      Post;
      Next;
    end;
  end;
  FreeQueryForUse(Q);
end;

procedure TCollectorRepForm.CheckDubl;
var
  AccCode :string;
begin
 AccCode:='';
 fSchetList.Clear;
 table1.First;
 while not table1.Eof do
 begin
   if AccCode<>table1.FieldByName('AccCode').AsString then
   begin
     AccCode:=table1.FieldByName('AccCode').AsString;
     fSchetList.Add(AccCode);
      stat1.Panels[0].Text:=FormatDateTime('HH:MM:SS',Now-StartTime);
      Application.ProcessMessages;
     table1.Next;
   end
   else
   begin
     table1.Delete;
   end;
 end;
end;

procedure TCollectorRepForm.ch_noutskPropertiesChange(Sender: TObject);
begin
 if ch_noutsk.Checked=True then IsPrincipal.Checked:=False;
end;
procedure TCollectorRepForm.clv1FlagPropertiesChange(Sender: TObject);
begin
  table1.Edit;
  table1.FieldByName('Flag').AsBoolean:= not table1.FieldByName('Flag').AsBoolean;
  table1.Post;
end;

{------------------------------------------------------------------------------
 MakeNewEvent:  Групповое назначение мероприятий
_______________________________________________________________________________}
procedure TCollectorRepForm.MakeNewEvent;
  var SQL: string;
      Q: TFIBQuery;
      D,T: TDateTime;
      Summa: currency;
      ECode,EName, EType, ENext: string;
      Form3: TNewMPForm;
      Note: string;
      i :Integer;
      Control: word;
      ls: TStringList;
 function GetCodes :TStringList;
 var
  i :Integer;
 begin
    table1.DisableControls;
    table1.First;
    for I := 0 to table1.RecordCount - 1 do
    begin
      if table1.FieldByName('Flag').AsBoolean=true then
      begin
        Result.Add(table1.FieldByName('ACCCODE').AsString);
      end;
      table1.Next;
    end;
    table1.EnableControls;
 end;

 function GetNewEvent: string;
  var
    Form2: TGetMListForm;
  begin
     Result:='';
     Form2:=TGetMListForm.Create(Application);
     Form2.Shablon:=GetProfileShablon;
     //Form2.Codes:=ENext;
     Form2.OpenDataSet;
     if Form2.DS.RecordCount=1 then Result:=Form2.DS.FN('Code').AsString
     else if Form2.DS.RecordCount<>0 then
      begin
       Form2.ShowModal;
       if Form2.ModalResult=mrOk then Result:=Form2.DS.FN('Code').AsString;
      end;
     if Result<>'' then EName:=Form2.DS.FN('Name').AsString;
     Form2.Free;
  end;


begin
   ls:=TStringList.Create;
   ls:=GetCodes;
   if ls.Text='' then Exit;
   ECode:=GetNewEvent;
   if ECode='' then Exit;
   //задаем параметры мероприятия
   Form3:=TNewMPForm.Create(Application);
   Form3.Caption:=EName;
   Control:=Form3.ShowModal;
   if Control=mrOk then
     begin
      D:=Form3.D.Date;
      T:=Form3.T.Time;
      Summa:=Form3.Summa.Value;
      Note:=Form3.Note.Lines.Text;
     end else begin D:=0; T:=0; Summa:=0; end;
   Form3.Free;
   if Control<>mrOk then Exit;
   //запись события
   SQL:='INSERT INTO Events(AccCode,EType,DActive,TActive,Note,Summa) '+
        'VALUES(:AccCode,:EType,:DActive,:TActive,:Note,:Summa)';
   Q:=GetQueryForUse(dm1.trnWr, SQL);
   try
    SetWaitCursor(true);
    for I := 0 to ls.Count - 1 do
    begin
      dm1.trnWr.StartTransaction;
      Q.ParamByName('AccCode').AsString:=ls[i];
      Q.ParamByName('EType').AsString:=ECode;
      Q.ParamByName('DActive').AsDate:=D;
      Q.ParamByName('TActive').AsTime:=T;
      Q.ParamByName('Note').AsString:=Note;
      Q.ParamByName('Summa').AsCurrency:=Summa;
      Q.ExecQuery;
      dm1.trnWr.Commit;
    end;
    //OpenEvents(true); //переоткрываем список событий
   finally
    SetWaitCursor(false);
    FreeQueryForUse(Q);
   end;
   //MakeFullList;
   ls.Free;
end;
{------------------------------------------------------------------------------
 N1Click:  Отметить все
_______________________________________________________________________________}
procedure TCollectorRepForm.N1Click(Sender: TObject);
var
  i: Integer;
begin
  if v1.Controller.IsFilterRowFocused then Exit;
    v1.BeginUpdate;
  try
      for I := 0 to v1.DataController.GridView.ViewData.RecordCount - 1 do
       begin
         v1.DataController.GridView.ViewData.Records[i].Focused := True;
         v1.DataController.SetEditValue
                         (v1.GetColumnByFieldName('Flag').Index,True, evsValue);
         v1.DataController.Post;
       end;
   finally
     v1.EndUpdate;
  end;
end;
{------------------------------------------------------------------------------
 N2Click:  Снять отметки
_______________________________________________________________________________}
procedure TCollectorRepForm.N2Click(Sender: TObject);
var
  i: Integer;
begin
  if v1.Controller.IsFilterRowFocused then Exit;
    v1.BeginUpdate;
  try
      for I := 0 to v1.DataController.GridView.ViewData.RecordCount - 1 do
       begin
         v1.DataController.GridView.ViewData.Records[i].Focused := True;
         v1.DataController.SetEditValue
                        (v1.GetColumnByFieldName('Flag').Index,false, evsValue);
         v1.DataController.Post;
       end;
   finally
     v1.EndUpdate;
  end;
end;
{------------------------------------------------------------------------------
TCollectorRepForm.N4Click:  Назначить мероприятия
_______________________________________________________________________________}
procedure TCollectorRepForm.N4Click(Sender: TObject);
begin
  MakeNewEvent;
end;

end.
